package com.boco.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.boco.entity.EMR_MZJYJL;

public class GetEMR_MZJYJLData {
	
	private static final Log log = LogFactory.getLog(GetEMR_MZJYJLData.class);
	public static List<EMR_MZJYJL> getData()
	{
	    Connection con = null;
	    PreparedStatement pre = null;
	    ResultSet result = null;
	    List<EMR_MZJYJL> re = new ArrayList<EMR_MZJYJL>();
	    try
	    {
	        Class.forName("oracle.jdbc.driver.OracleDriver");
	        System.out.println("开始尝试连接数据库！");
	        String url = "jdbc:oracle:" + "thin:@172.20.21.12:1521:orcl";
	        String user = "sjzx_hxk";
	        String password = "boco#1234";
	        con = DriverManager.getConnection(url, user, password);
	        System.out.println("连接成功！");
	        
	        String sql = "select "
	        		+"'1' as HZZSYH,"
					+"a.yljgdm as YLJGZZJGDM,"
					+"'' as YLJGMC,"
					+"a.jzlsh as MJZH,"
					+"'1' as JXXMLSH,"
					+"a.bgdh as JYBGDBH,"
					+"a.dzsqdbh as DZSQDBH,"
					+"a.brxm as XM,"
					+"'' as HZLX,"
					+"a.brxb as XB,"
					 +"regexp_substr(a.brnl,'[0-9]+') as NL,"
					+"a.sqks as KSDM,"
					+"a.sqksmc as KSMC,"
					+"a.bq as BQMC,"
					+"a.bfh as BFH,"
					+"a.ch as BCH,"
					+"a.yljgdm as JYSQJGDM,"
					+"'' as JYSQJG,"
					+"a.sqks as JYSQKSDM,"
					+"a.sqksmc as JYSQKS,"
					+"a.yljgdm as JYBGJGDM,"
					+"'' as JYBGJG,"
					+"'' as JYBGKSDM,"
					+"a.jybgks as JYBGKS,"
					+"'' as JYBGJG2,"
					+"a.bgbz as JYBGBZ,"
					+"to_char(a.jyrq,'YYYY-MM-dd HH24:mi:ss') as JYBGRQ,"
					+"a.yljgdm as ZDJGDM,"
					+"'' as ZDJGMC,"
					+"to_char(a.sqrq,'YYYY-MM-dd HH24:mi:ss') as ZDRQ,"
					+"'' as JBZDMC,"
					+"'' as JBZDDM,"
					+"a.bgrgh as BGYSGH,"
					+"a.bgrxm as BGYSQM,"
					+"a.shrgh as SHYSGH,"
					+"a.shrxm  as SHYSQM,"
					+"'' as JYXMMC,"
					+"'' as ZRLSH,"	
					+"to_char(a.ywscsj,'YYYY-MM-dd HH24:mi:ss') as BCRQ,"
					+"'' as ORGCODE,"
					+"'' as AREACODE,"
					+"to_char(sysdate,'yyyyMMddHH24miss') as UPLOADTIME,"
					+"'' as  SOURCEID "
					+" from TB_LIS_JYBG a where rownum < 100";
	 
	        log.info("查询sql："+sql);
	        pre = con.prepareStatement(sql);
	        result = pre.executeQuery();
	        System.out.println("执行成功");
	        while (result.next()){
//	            System.out.println(result.getInt("YYDAH"));
	        	EMR_MZJYJL temp = new EMR_MZJYJL();
	        	temp.setHZZSYH(result.getString("HZZSYH"));
	        	temp.setYLJGZZJGDM(result.getString("YLJGZZJGDM"));
	        	temp.setYLJGMC(result.getString("YLJGMC"));
	        	temp.setMJZH(result.getString("MJZH"));
	        	temp.setJXXMLSH(result.getString("JXXMLSH"));
	        	temp.setJYBGDBH(result.getString("JYBGDBH"));
	        	temp.setDZSQDBH(result.getString("DZSQDBH"));

	        	temp.setXM(result.getString("XM"));
	        	temp.setHZLX(result.getString("HZLX"));
	        	temp.setXB(result.getString("XB"));
	        	temp.setNL(result.getString("NL"));
	        	temp.setKSDM(result.getString("KSDM"));
	        	temp.setKSMC(result.getString("KSMC"));
	        	temp.setBQMC(result.getString("BQMC"));
	        	temp.setBFH(result.getString("BFH"));
	        	temp.setBCH(result.getString("BCH"));
	        	temp.setJYSQJGDM(result.getString("JYSQJGDM"));
	        	temp.setJYSQJG(result.getString("JYSQJG"));
	        	temp.setJYSQKSDM(result.getString("JYSQKSDM"));
	        	temp.setJYSQKS(result.getString("JYSQKS"));
	        	temp.setJYBGJGDM(result.getString("JYBGJGDM"));
	        	temp.setJYBGJG(result.getString("JYBGJG"));
	        	temp.setJYBGKSDM(result.getString("JYBGKSDM"));
	        	temp.setJYBGKS(result.getString("JYBGKS"));
	        	temp.setJYBGJG2(result.getString("JYBGJG2"));
	        	temp.setJYBGBZ(result.getString("JYBGBZ"));
	        	temp.setJYBGRQ(result.getString("JYBGRQ"));
	        	temp.setZDJGDM(result.getString("ZDJGDM"));
	        	temp.setZDJGMC(result.getString("ZDJGMC"));
	        	temp.setZDRQ(result.getString("ZDRQ"));
	        	temp.setJBZDMC(result.getString("JBZDMC"));
	        	temp.setJBZDDM(result.getString("JBZDDM"));
	        	temp.setBGYSGH(result.getString("BGYSGH"));
	        	temp.setBGYSQM(result.getString("BGYSQM"));
	        	temp.setSHYSGH(result.getString("SHYSGH"));
	        	temp.setSHYSQM(result.getString("SHYSQM"));
	        	temp.setJYXMMC(result.getString("JYXMMC"));

	        	temp.setZRLSH(result.getString("ZRLSH"));
	        	temp.setBCRQ(result.getString("BCRQ"));
	        	temp.setORGCODE(result.getString("ORGCODE"));
	        	temp.setAREACODE(result.getString("AREACODE"));
	        	temp.setUPLOADTIME(result.getString("UPLOADTIME"));
	        	temp.setSOURCEID(result.getString("SOURCEID"));
	        	re.add(temp);
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }
	    finally
	    {
	        try
	        {
	            if (result != null)
	                result.close();
	            if (pre != null)
	                pre.close();
	            if (con != null)
	                con.close();
	            System.out.println("数据库连接已关闭！");
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	        }
	    }
		return re;
	}
}
